package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.IFacadeUserDao;
import com.sq.partner.manager.mybatis.model.FacadeUser;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class FacadeUserDaoImpl extends BaseDaoImpl implements IFacadeUserDao {

	private static Logger logger = LogUtil.getLog(FacadeUserDaoImpl.class);

	@Override
	public Page queryPage(Page page, Integer pageIndex, FacadeUser user)
			throws Exception {
		Connection conn = null;
		// 查总条数
		PreparedStatement pstmt = null;
		// 查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page p = null;
		try {
			List<String> list = new ArrayList<String>();
			conn = this.getConnection();
			StringBuffer sqlbuf = new StringBuffer();
			sqlbuf.append("SELECT U.USERID,U.USER_CODE,U.FK_CHANNEL_CODE,U.USER_NAME,U.TELEPHONE,"
					+ " U.EMAIL,DECODE(U.STATUS, 1, '有效', '无效') AS STATUS,U.USER_TYPE AS USERTYPECODE,"
					+ " CASE WHEN U.USER_TYPE = '1' THEN '合作伙伴用户'  WHEN U.USER_TYPE = '2' THEN '中介公司用户' WHEN U.USER_TYPE = '3' THEN '个人代理' WHEN U.USER_TYPE = '4' THEN '总对总代理'"
					+ " END USERTYPEDSCP,M.SUB_CHANNEL_CODE AS FKSUBCHANNELCODE,"
					+ " M.SUB_CHANNEL_NAME AS FKSUBCHANNELDESC ,"
					+ " CASE WHEN U.USER_TYPE = '3' THEN "
					+ " '<a href=''javascript:void(0)'' onClick=''warnResetPwd(this)''>重置密码</a> <a href=''javascript:void(0)'' onClick=''unlock(this)''>解锁</a>'"
					+" ELSE"
					+" ' <a href=''javascript:void(0)'' onClick=''editUser(this);return false''>编辑</a> <a href=''javascript:void(0)'' onClick=''warn(this)''>删除</a> <a href=''javascript:void(0)'' onClick=''warnResetPwd(this)''>重置密码</a> <a href=''javascript:void(0)'' onClick=''unlock(this)''>解锁</a>'"
					+" END  operate_type "
					+ " FROM T_FACADE_USER U INNER JOIN (SELECT USER1.USERID, G.SUB_CHANNEL_CODE, G.SUB_CHANNEL_NAME"
					+ " FROM T_GATEWAY_SUB_CHANNEL G INNER JOIN T_GATEWAY_CHANNEL_INFO H ON G.FK_CHANNEL_CODE = H.CHANNEL_CODE");
			if(!user.isHQFlag())
			{
				String str=user.getDepartmentStr();
				String userDept="";
				if(StrUtil.isNotEmpty(str)){
					String[] depts= str.split(",");
					for (String string : depts) {
						userDept+=" union select  "+ string+ " from dual";
					}
				}
				sqlbuf.append(" AND h.dept_code IN ( SELECT DEPT_CODE FROM T_CORE_DEPT WHERE DEPT_CODE LIKE '00%' "+userDept+" )");
			}
			sqlbuf.append(" INNER JOIN T_FACADE_USER USER1 ON USER1.FK_SUB_CHANNEL_CODE = G.SUB_CHANNEL_CODE  AND USER1.FK_CHANNEL_CODE=G.FK_CHANNEL_CODE "
					+ " AND USER1.USER_TYPE IN('1','4') UNION ALL SELECT USER2.USERID, KK.AGENCY_CODE, KK.AGENCY_NAME"
					+ " FROM T_GATEWAY_AGENCY KK  INNER JOIN T_FACADE_USER USER2 ON USER2.AGENCY_CODE = KK.AGENCY_CODE"
					+ " AND USER2.USER_TYPE = '2'");
			if(!user.isHQFlag())
			{
				sqlbuf.append(" and kk.dept_code IN  ("+user.getDepartmentStr()+")");
			}
			sqlbuf.append(" UNION ALL SELECT USER3.USERID, GG.USER_CODE, CI.CHANNEL_NAME"
					+ " FROM T_TRAD_AGENT_INFO GG INNER JOIN T_GATEWAY_CHANNEL_INFO CI ON GG.CHANNEL_CODE = CI.CHANNEL_CODE");
			if(!user.isHQFlag())
			{
				sqlbuf.append(" AND ci.dept_code IN ("+user.getDepartmentStr()+")");
			}
			sqlbuf.append(" INNER JOIN T_FACADE_USER USER3 ON USER3.USER_CODE = GG.USER_CODE AND USER3.USER_TYPE = '3'"
					+ " AND GG.AGENT_TYPE = '3'  and user3.FK_SUB_CHANNEL_CODE  =  NVL(GG.SUB_CHANNEL_CODE, GG.CHANNEL_CODE) ) M ON M.USERID = U.USERID  ");
			//查询当前机构的所有子机构的用户信息
			if(null != user.getfKSubChannelCode()
					&& StrUtil.isNotEmpty(user.getfKSubChannelCode())){
				sqlbuf.append(" INNER JOIN (SELECT G.SUB_CHANNEL_CODE FROM T_GATEWAY_SUB_CHANNEL G"
						+ " CONNECT BY NOCYCLE PRIOR G.SUB_CHANNEL_CODE = G.PARENT_CHANNEL_CODE START WITH G.SUB_CHANNEL_CODE = ?) DT "
						+ " ON DT.SUB_CHANNEL_CODE = U.FK_SUB_CHANNEL_CODE");
				list.add(user.getfKSubChannelCode());
			}
			sqlbuf.append(" WHERE 1 = 1");
			String sql;
			if (null != user && StrUtil.isNotEmpty(user.getUserCode())) {
				sqlbuf.append(" and u.user_code like ? ");
				list.add("%" + user.getUserCode() + "%");
			}
			if (null != user && StrUtil.isNotEmpty(user.getUserName())) {
				sqlbuf.append(" and u.user_name like ? ");
				list.add("%" + user.getUserName() + "%");
			}
			if (null != user && StrUtil.isNotEmpty(user.getUserTypeCode())) {
				if("1".equals(user.getUserTypeCode())){
					sqlbuf.append(" and ( u.user_type =  ? or u.user_type =  ? ) "); 
					list.add("1");
					list.add("4");
				}else{
					sqlbuf.append(" and u.user_type =  ? ");
					list.add(user.getUserTypeCode());
				}
			}
			if (null != user && StrUtil.isNotEmpty(user.getAgencyCode())) {
				sqlbuf.append(" and u.agency_code = ? ");
				list.add(user.getAgencyCode());
			}
			sqlbuf.append(" order by  u.oper_time desc, u.userid desc ");
			sql = sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from (" + sql + ")");
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i + 1, list.get(i));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			// 查出分页
			p = new Page(page.getCurrent(), total);
			SqlUtil sqlUtil = new SqlUtil();
			String pagesql = sqlUtil.buildPageSqlForOracle(sql, p).toString();
			logger.debug("执行SQL：" + pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				pstmt1.setObject(i + 1, list.get(i));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			p.setData(lists);
		} finally {
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return p;
	}

	@Override
	public boolean addUser(FacadeUser user) throws Exception {
		return this.add("insertFacadeUser", user);
	}

	@Override
	public FacadeUser queryUserCountByMap(Map<String, String> map)
			throws Exception {
		FacadeUser fuser = (FacadeUser) this.query("queryFacadeUserByMap", map);
		if (null != fuser) {
			return fuser;
		}
		return null;
	}

	@Override
	public boolean saveUser(FacadeUser user) throws Exception {
		return this.update("updateFacadeUserNotNull", user);
	}

	@Override
	public FacadeUser queryUserById(Long userid) throws Exception {
		FacadeUser fuser = (FacadeUser) this.query("queryFacadeUserById",
				userid);
		if (null != fuser) {
			return fuser;
		}
		return null;
	}

	@Override
	public boolean delUserById(Long userid) throws Exception {
		return this.delete("delFacadeUserById", userid);
	}

	@Override
	public boolean updateUser(FacadeUser user) throws Exception {
		return this.update("updateFacadeUser", user);
	}

	@Override
	public boolean delUserByUserCodeAndUserType(String userCode, String userTypeCode) throws Exception {
		FacadeUser user = new FacadeUser();
		user.setUserCode(userCode);
		user.setUserTypeCode(userTypeCode);
		return this.delete("delFacadeUserByUserCodeAndUserType", user);
	}

	@Override
	public void addBatchMtFacadeUserInfo(List<FacadeUser> list) throws AppException {
		if(null!=list && list.size()>0){
			try {
				this.add("insertBatchMtFacadeUserInfoList", list);
			} catch (Exception e) {
				logger.error(e);
				throw new AppException("数据库应用忙，请稍后重试！");
			}
		}
	}

	@Override
	public List<FacadeUser> queryUsersByMapUser(FacadeUser user) throws Exception {
		return this.queryList("queryFacadeUserByMap", user);
	}
	
	@Override
	public List<FacadeUser> queryUsersByUserCodeStr(String UserCodeStr) throws Exception {
		return this.queryList("queryUsersByUserCodeStr", UserCodeStr);
	}

	@Override
	public boolean delUserBySubChannelCodeAndUserType(String subChannelCode,String userType) throws Exception {
		FacadeUser user = new FacadeUser();
		user.setfKSubChannelCode(subChannelCode);
		user.setUserTypeCode(userType);
		return this.delete("delFacadeUserBySubChannelCodeAndUserType", user);
	}
	
}
